package dao.Impl;

import context.BaseContext;
import dao.BaseDao;
import dao.OrderDao;
import model.VO.OrderBackVO;
import model.VO.OrderVO;
import model.VO.OrderWeekTurnoverVO;
import model.entity.Order;
import model.entity.OrderDetail;
import util.NowTime;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl extends BaseDao implements OrderDao {
    //取消订单
    @Override
    public void cancel(int id, String cancelReason,int payStatus) {
        String sql="update `order` set status=?,cancel_reason=? ,cancel_time=?,pay_status=? where id=?;";
        NowTime nowTime = new NowTime();
        String t = nowTime.getTime();
        try{
            pstmt=conn.prepareStatement(sql);
            if(payStatus==0){
                pstmt.setInt(1,6);
            }else{
                pstmt.setInt(1,2);
            }
            pstmt.setString(2,cancelReason);
            pstmt.setString(3,t);
            pstmt.setInt(4,payStatus);
            pstmt.setInt(5,id);
            pstmt.executeUpdate();
            System.out.println("取消订单成功");
        } catch (SQLException e) {
            System.out.println("取消订单失败"+e);
        }
    }

    //完成订单
    @Override
    public void complete(int id) {
        String sql="update `order` set status=5 where id=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
            System.out.println("完成订单成功");
        } catch (SQLException e) {
            System.out.println("完成订单失败"+e);
        }
    }

    @Override
    public String order(float amount, int payMethod, String remark,int type,String phone,String name,String shop_address) {
        String sql="insert into `order` (number, status, user_id, order_time, pay_method, pay_status, amount, remark, phone, shop_id, shop_address, user_name,type)value(?,?,?,?,?,?,?,?,?,?,?,?,?);";
        OrderBackVO vo=new OrderBackVO();
//        String number=RandomNum.getNumber();
        String number= String.valueOf(System.currentTimeMillis());
        NowTime time=new NowTime();
        try {
            pstmt=conn.prepareStatement(sql);
            //生成订单号
            pstmt.setString(1,number);
            //默认待付款
            pstmt.setInt(2,1);
            pstmt.setInt(3, BaseContext.getCurrentId());
            pstmt.setTimestamp(4, Timestamp.valueOf(time.getTime()));
            pstmt.setInt(5,payMethod);
            //默认未支付
            pstmt.setInt(6,0);
            pstmt.setFloat(7,amount);
            pstmt.setString(8,remark);
            pstmt.setString(9,phone);
            //默认店铺
            pstmt.setInt(10,1);
            pstmt.setString(11,shop_address);
            pstmt.setString(12,name);
            pstmt.setInt(13,type);
            pstmt.executeUpdate();
            System.out.println("创建订单成功!");
        }
        catch (SQLException e){
            System.out.println("创建订单失败!"+e);
        }
        return number;
    }
    @Override
    public void orderDetail(int dishId, int setmealId,int categpryId,String name, String image, int orderId, int number, float amount) {
        String sql="insert into order_detail (name, image, order_id, dish_id, setmeal_id,number,amount,category_id) value (?,?,?,?,?,?,?,?);";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setString(2,image);
            pstmt.setInt(3,orderId);
            pstmt.setInt(4,dishId);
            pstmt.setInt(5,setmealId);
            pstmt.setInt(6,number);
            pstmt.setFloat(7,amount);
            pstmt.setInt(8,categpryId);
            pstmt.executeUpdate();
            System.out.println("创建订单细节成功!");
        }
        catch (SQLException e){
            System.out.println("创建订单细节失败!"+e);
        }
    }


    @Override
    public Order getByNumber(String number) {
        Order o=new Order();
        String sql="select * from `order` where number=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,number);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                o.setId(rs.getInt("id"));
                o.setAmount(rs.getFloat("amount"));
                o.setNumber(rs.getString("number"));
                o.setPhone(rs.getString("phone"));
                o.setType(rs.getInt("type"));
                o.setOrder_time(String.valueOf(rs.getTimestamp("order_time")));
                o.setCancel_time(String.valueOf(rs.getTimestamp("cancel_time")));
                o.setCheckout_time(String.valueOf(rs.getTimestamp("checkout_time")));
                o.setCancel_reason(rs.getString("cancel_reason"));
                o.setPay_method(rs.getInt("pay_method"));
                o.setCancel_time(String.valueOf(rs.getTimestamp("cancel_time")));
                o.setRemark(rs.getString("remark"));
                o.setPay_status(rs.getInt("pay_status"));
                o.setUser_id(rs.getInt("user_id"));
                o.setShop_id(rs.getInt("shop_id"));
                o.setUser_name(rs.getString("name"));
                o.setShop_address(rs.getString("shop_address"));
            }
            System.out.println("根据订单号查询订单成功!");
        }
        catch (SQLException e){
            System.out.println("根据订单号查询订单失败!"+e);
        }
        return o;
    }

    //后台订单分页查询
    @Override
    public List<Order> pageQuery(int page, int pageSize, String status, String number, String orderTime) {
        List<Order> orderList=new ArrayList<>();
        String sql="select * from `order` where 1=1";
        if(status!=null&&status!=""){
            sql+=" and status="+status;
        }
        if(number!=null&&number!=""){
            sql+=" and number="+number;
        }
        if(orderTime!=null&&orderTime!=""){
            sql+=" and DATE(order_time)='"+orderTime+"'";
        }
        sql+=" order by order_time desc limit ?,?;";
        page=(page-1)*pageSize;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, page);
            pstmt.setInt(2,pageSize);
            pstmt.executeQuery();
            rs = pstmt.getResultSet();
            while (rs.next()){
                Order order=new Order();
                order.setId(rs.getInt("id"));
                order.setNumber(rs.getString("number"));
                order.setStatus(rs.getInt("status"));
                order.setUser_id(rs.getInt("user_id"));
                order.setOrder_time(rs.getString("order_time"));
                order.setCheckout_time(rs.getString("checkout_time"));
                order.setPay_method(rs.getInt("pay_method"));
                order.setPay_status(rs.getInt("pay_status"));
                order.setAmount(rs.getFloat("amount"));
                order.setType(rs.getInt("type"));
                order.setRemark(rs.getString("remark"));
                order.setPhone(rs.getString("phone"));
                order.setShop_id(rs.getInt("shop_id"));
                order.setShop_address(rs.getString("shop_address"));
                order.setUser_name(rs.getString("user_name"));
                order.setCancel_time(rs.getString("cancel_time"));
                order.setCancel_reason(rs.getString("cancel_reason"));
                order.setTakeout_number(rs.getString("takeout_number"));
                order.setShop_reply(rs.getString("shop_reply"));
                orderList.add(order);
            }
        }catch (SQLException e){
            System.out.println("分页查询出错："+e);
        }
        return orderList;
    }

    @Override
    public OrderVO getOrderInfo(int id) {
        OrderVO orderVO=new OrderVO();
        String sql="select * from `order` where id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeQuery();
            rs = pstmt.getResultSet();
            while (rs.next()){
                orderVO.setId(rs.getInt("id"));
                orderVO.setNumber(rs.getString("number"));
                orderVO.setStatus(rs.getInt("status"));
                orderVO.setUser_id(rs.getInt("user_id"));
                orderVO.setOrder_time(rs.getString("order_time"));
                orderVO.setCheckout_time(rs.getString("checkout_time"));
                orderVO.setPay_method(rs.getInt("pay_method"));
                orderVO.setPay_status(rs.getInt("pay_status"));
                orderVO.setAmount(rs.getFloat("amount"));
                orderVO.setType(rs.getInt("type"));
                orderVO.setRemark(rs.getString("remark"));
                orderVO.setPhone(rs.getString("phone"));
                orderVO.setShop_id(rs.getInt("shop_id"));
                orderVO.setShop_address(rs.getString("shop_address"));
                orderVO.setUser_name(rs.getString("user_name"));
                orderVO.setCancel_time(rs.getString("cancel_time"));
                orderVO.setCancel_reason(rs.getString("cancel_reason"));
                orderVO.setTakeout_number(rs.getString("takeout_number"));
                orderVO.setShop_reply(rs.getString("shop_reply"));
            }
        }catch (SQLException e){
            System.out.println("查询订单详情出错："+e);
        }

        List<OrderDetail> orderDetailList=new ArrayList<>();
        sql="select * from `order_detail` where order_id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeQuery();
            rs = pstmt.getResultSet();
            while (rs.next()){
                OrderDetail orderDetail=new OrderDetail();
                orderDetail.setId(rs.getInt("id"));
                orderDetail.setName(rs.getString("name"));
                orderDetail.setImage(rs.getString("image"));
                orderDetail.setOrder_id(rs.getInt("order_id"));
                orderDetail.setDish_id(rs.getInt("dish_id"));
                orderDetail.setSetmeal_id(rs.getInt("setmeal_id"));
                orderDetail.setCategory_id(rs.getInt("category_id"));
                orderDetail.setNumber(rs.getInt("number"));
                orderDetail.setAmount(rs.getFloat("amount"));
                orderDetailList.add(orderDetail);
            }
        }catch (SQLException e){
            System.out.println("查询订单详情出错："+e);
        }
        orderVO.setOrderDetailList(orderDetailList);
        return orderVO;
    }

    @Override
    public void pickMeal(int id) {
        String sql="update `order` set status=4 where id=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
            System.out.println("修改订单状态为请取餐成功");
        } catch (SQLException e) {
            System.out.println("修改订单状态为请取餐失败"+e);
        }
    }

    @Override
    public int getOrderAll() {
        int allOrderCount=0;
        String sql="select count(id) as `allOrderCount` from `order`;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.executeQuery(sql);
            rs=pstmt.getResultSet();
            while (rs.next()){
                allOrderCount=rs.getInt("allOrderCount");
            }
        } catch (Exception e) {
            System.out.println("查询所有订单数失败："+e);
        }
        return allOrderCount;
    }

    @Override
    public int getDayOrderAll() {
        int allDayOrderCount=0;
        String sql="select count(id) as `allDayOrderCount` from `order` where DATE (order_time)=curdate();";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.executeQuery(sql);
            rs=pstmt.getResultSet();
            while (rs.next()){
                allDayOrderCount=rs.getInt("allDayOrderCount");
            }
        } catch (Exception e) {
            System.out.println("查询所有订单数失败："+e);
        }
        return allDayOrderCount;
    }

    @Override
    public double getTurnoverAll() {
        double allTurnover=0;
        String sql="select sum(amount) as `allTurnover` from `order` where status=5;";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                allTurnover=rs.getInt("allTurnover");
            }
        } catch (Exception e) {
            System.out.println("查询总营业额失败："+e);
        }
        return allTurnover;
    }

    @Override
    public double getDayTurnoverAll() {
        double allTurnover=0;
        String sql="select sum(amount) as `allTurnover` from `order` where status=5 and DATE (order_time)=curdate();";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                allTurnover=rs.getInt("allTurnover");
            }
        } catch (Exception e) {
            System.out.println("查询总营业额失败："+e);
        }
        return allTurnover;
    }

    @Override
    public int getPayCountAll() {
        int totalPayCount=0;
        String sql="select count(id) as `totalPayCount` from `order` where pay_status=1";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                totalPayCount=rs.getInt("totalPayCount");
            }
        } catch (Exception e) {
            System.out.println("查询总支付量失败："+e);
        }
        return totalPayCount;
    }

    @Override
    public int getDayPayCountAll() {
        int PayCount=0;
        String sql="select count(id) as `PayCount` from `order` where pay_status=1 and DATE (order_time)=curdate()";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                PayCount=rs.getInt("PayCount");
            }
        } catch (Exception e) {
            System.out.println("查询总支付量失败："+e);
        }
        return PayCount;
    }

    @Override
    public List<OrderWeekTurnoverVO> getWeekTurnover() {
        List<OrderWeekTurnoverVO> turnoverList=new ArrayList<>();
        String sql="SELECT\n" +
                "    o.Date,\n" +
                "    COALESCE(SUM(od.amount), 0) AS turnover\n" +
                "FROM (\n" +
                "         SELECT\n" +
                "             DATE(order_time) AS Date\n" +
                "         FROM\n" +
                "             `order`\n" +
                "         WHERE\n" +
                "             order_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()\n" +
                "         GROUP BY\n" +
                "             DATE(order_time)\n" +
                "     ) AS o\n" +
                "         LEFT JOIN `order` AS od ON o.Date = DATE(od.order_time) and od.status=5\n" +
                "GROUP BY\n" +
                "    o.Date\n" +
                "ORDER BY\n" +
                "    o.Date;";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                OrderWeekTurnoverVO orderWeekTurnoverVO=new OrderWeekTurnoverVO();
                orderWeekTurnoverVO.setDate(rs.getString("Date"));
                orderWeekTurnoverVO.setTurnover(rs.getInt("turnover"));
                turnoverList.add(orderWeekTurnoverVO);
            }
        } catch (Exception e) {
            System.out.println("查询近七日营业额错误："+e);
        }
        return turnoverList;
    }

    @Override
    public int getDayValidOrderCount() {
        int validOrderCount=0;
        String sql="select count(id) as `validOrderCount` from `order` where status=5 and DATE(order_time)=curdate();";
        try{
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                validOrderCount=rs.getInt("validOrderCount");
            }
        } catch (Exception e) {
            System.out.println("查询今日有效订单失败："+e);
        }
        return validOrderCount;
    }

    @Override
    public int getMonthOrderCount() {
        int monthOrderCount=0;
        String sql="select count(id) as `monthOrderCount` from `order` " +
                "where DATE (order_time) >=DATE_ADD(CURDATE(), INTERVAL 1-DAYOFMONTH(CURDATE()) DAY) " +
                "and DATE (order_time) <LAST_DAY(CURDATE());";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                monthOrderCount=rs.getInt("monthOrderCount");
            }
        } catch (Exception e) {
            System.out.println("查询本月订单数失败："+e);
        }
        return monthOrderCount;
    }

    @Override
    public int getMonthValidOrderCount() {
        int monthValidOrderCount=0;
        String sql="select count(id) as `monthValidOrderCount` from `order` " +
                "where status=5 and DATE (order_time) >=DATE_ADD(CURDATE(), INTERVAL 1-DAYOFMONTH(CURDATE()) DAY) " +
                "and DATE (order_time) <LAST_DAY(CURDATE());";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                monthValidOrderCount=rs.getInt("monthValidOrderCount");
            }
        } catch (Exception e) {
            System.out.println("查询本月订单数失败："+e);
        }
        return monthValidOrderCount;
    }

    @Override
    public double getMonthTurnover() {
        double monthTurnover=0;
        String sql="select sum(amount) as `monthTurnover` from `order` " +
                "where status=5 and DATE (order_time) >=DATE_ADD(CURDATE(), INTERVAL 1-DAYOFMONTH(CURDATE()) DAY) " +
                "and DATE (order_time) <LAST_DAY(CURDATE());";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                monthTurnover=rs.getDouble("monthTurnover");
            }
        } catch (Exception e) {
            System.out.println("查询本月营业额失败："+ e);
        }
        return monthTurnover;
    }

    @Override
    public int getCanteenCount() {
        int canteenCount=0;
        String sql="select count(id) as `canteenCount` from `order` where status=5 and type=1 and DATE(order_time)=curdate();";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                canteenCount=rs.getInt("canteenCount");
            }
        } catch (Exception e) {
            System.out.println("查询今日堂食数量错误："+e);
        }
        return canteenCount;
    }

    @Override
    public int getToGoCount() {
        int toGoCount=0;
        String sql="select count(id) as `toGoCount` from `order` where status=5 and type=2 and DATE(order_time)=curdate();";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                toGoCount=rs.getInt("toGoCount");
            }
        } catch (Exception e) {
            System.out.println("查询今日外带数量错误："+e);
        }
        return toGoCount;
    }


    @Override
    public void payment(Order order) {
        String sql="update `order` set status=?,checkout_time=?,pay_method=?,pay_status=?,takeout_number=? where number=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,order.getStatus());
            pstmt.setString(2,order.getCheckout_time());
            pstmt.setInt(3,order.getPay_method());
            pstmt.setInt(4,order.getPay_status());
            pstmt.setString(5,order.getTakeout_number());
            pstmt.setString(6,order.getNumber());
            pstmt.executeUpdate();
        } catch (Exception e) {
            System.out.println("用户支付错误："+e);
        }
    }

    //查询历史订单
    @Override
    public List<OrderVO> list(String status) {
        List<OrderVO> orderVOList=new ArrayList<>();
        String sql="select * from `order` where user_id=?";
        if(status!=null&&status!=""){
            sql+= " and status="+Integer.parseInt(status);
        }
        try {

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,BaseContext.getCurrentId());
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                OrderVO orderVO = new OrderVO();
                orderVO.setId(rs.getInt("id"));
                orderVO.setNumber(rs.getString("number"));
                orderVO.setStatus(rs.getInt("status"));
                orderVO.setUser_id(rs.getInt("user_id"));
                orderVO.setOrder_time(rs.getString("order_time"));
                orderVO.setCheckout_time(rs.getString("checkout_time"));
                orderVO.setPay_method(rs.getInt("pay_method"));
                orderVO.setPay_status(rs.getInt("pay_status"));
                orderVO.setAmount(rs.getFloat("amount"));
                orderVO.setType(rs.getInt("type"));
                orderVO.setRemark(rs.getString("remark"));
                orderVO.setPhone(rs.getString("phone"));
                orderVO.setShop_id(rs.getInt("shop_id"));
                orderVO.setShop_address(rs.getString("shop_address"));
                orderVO.setUser_name(rs.getString("user_name"));
                orderVO.setCancel_time(rs.getString("cancel_time"));
                orderVO.setCancel_reason(rs.getString("cancel_reason"));
                orderVO.setTakeout_number(rs.getString("takeout_number"));
                orderVO.setShop_reply(rs.getString("shop_reply"));
                orderVOList.add(orderVO);
            }


            String sqlOd="select * from `order_detail` where order_id=?;";
            for(OrderVO orderVO1:orderVOList){
                List<OrderDetail> orderDetailList=new ArrayList<>();
                pstmt=conn.prepareStatement(sqlOd);
                pstmt.setInt(1,orderVO1.getId());
                pstmt.executeQuery();
                rs=pstmt.getResultSet();
                while (rs.next()) {
                    OrderDetail orderDetail = new OrderDetail();
                    orderDetail.setId(rs.getInt("id"));
                    orderDetail.setName(rs.getString("name"));
                    orderDetail.setImage(rs.getString("image"));
                    orderDetail.setOrder_id(rs.getInt("order_id"));
                    orderDetail.setDish_id(rs.getInt("dish_id"));
                    orderDetail.setSetmeal_id(rs.getInt("setmeal_id"));
                    orderDetail.setNumber(rs.getInt("number"));
                    orderDetail.setAmount(rs.getFloat("amount"));
                    orderDetailList.add(orderDetail);
                }
                orderVO1.setOrderDetailList(orderDetailList);
            }
        } catch (Exception e) {
            System.out.println("查询历史订单失败："+e);
        }
        return orderVOList;
    }
    //根据订单id查询订单
    @Override
    public Order getById(int id) {
        Order o=new Order();
        String sql="select * from `order` where id=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                o.setId(rs.getInt("id"));
                o.setAmount(rs.getFloat("amount"));
                o.setNumber(rs.getString("number"));
                o.setPhone(rs.getString("phone"));
                o.setStatus(rs.getInt("status"));
                o.setType(rs.getInt("type"));
                o.setOrder_time(String.valueOf(rs.getTimestamp("order_time")));
                o.setCancel_time(String.valueOf(rs.getTimestamp("cancel_time")));
                o.setCheckout_time(String.valueOf(rs.getTimestamp("checkout_time")));
                o.setCancel_reason(rs.getString("cancel_reason"));
                o.setPay_method(rs.getInt("pay_method"));
                o.setRemark(rs.getString("remark"));
                o.setPay_status(rs.getInt("pay_status"));
                o.setUser_id(rs.getInt("user_id"));
                o.setShop_id(rs.getInt("shop_id"));
                o.setUser_name(rs.getString("user_name"));
                o.setShop_address(rs.getString("shop_address"));
                o.setShop_reply(rs.getString("shop_reply"));
                o.setTakeout_number(rs.getString("takeout_number"));
            }
            System.out.println("根据订单id查询订单成功!");
        }
        catch (SQLException e){
            System.out.println("根据订单id查询订单失败!"+e);
        }
        return o;
    }

    @Override
    public void refund(int id, int status, int payStatus, String shopReply) {
        String sql="update `order` set status=?,pay_status=?,shop_reply=? where id=?;";
        try {
            pstmt= conn.prepareStatement(sql);
            pstmt.setInt(1,status);
            pstmt.setInt(2,payStatus);
            pstmt.setString(3,shopReply);
            pstmt.setInt(4,id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("商家是否同意退款出错："+e);
        }
    }
}
